﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Threading.Tasks;
using System.Data.SqlClient;
using System.Data;
using Model;

namespace DAL
{
    public class  WebDAL
    {
        /// <summary>
        /// 链接字符串
        /// </summary>
        SqlConnection con = new SqlConnection(" Data Source=PC201609040948;Initial Catalog=TianTianDai_Db;Persist Security Info=True;User ID=sa;Pwd=123456");
        /// <summary>
        /// 网站公告显示功能
        /// </summary>
        /// <returns></returns>
        public List<Notice> Web_Show()
        {
            con.Open();
            string sql = "select * from Notice";
            SqlDataAdapter adp = new SqlDataAdapter(sql, con);
            DataTable table = new DataTable();
            adp.Fill(table);
            con.Close();
            List<Notice> list = new List<Notice>();
            foreach (DataRow item in table.Rows)
            {
                Notice m = new Notice();
                m.NoticeContent = item["NoticeContent"].ToString();
                m.NoticeId = Convert.ToInt32(item["NoticeId"]);
                m.NoticePerson = item["NoticePerson"].ToString();
                m.NoticeTime = Convert.ToDateTime(item["NoticeTime"].ToString());
                m.NoticeName = item["NoticeName"].ToString();
                
                list.Add(m);
            }
            return list;

        }
        /// <summary>
        /// 网站公告的添加
        /// </summary>
        /// <param name="m"></param>
        /// <returns></returns>
        public int Web_Insert(Notice m)
        {
            con.Open();
            string sql = "inset into Notice values ('" + m.NoticeContent + "','" + m.NoticePerson + "','" + m.NoticeTime + "','" + m.NoticeName + "')";
            SqlCommand com = new SqlCommand(sql, con);
            return com.ExecuteNonQuery();
        }
        /// <summary>
        /// 网站公告的删除
        /// </summary>
        /// <param name="id"></param>
        /// <returns></returns>
        public int Web_Delete(int id)
        {
            con.Open();
            string sql = "Delete from Notice where NoticeId='" + id + "'";
            SqlCommand com = new SqlCommand(sql, con);
            return com.ExecuteNonQuery();
        }


        /// <summary>
        /// 网站公告的显示或修改第一步
        /// </summary>
        /// <param name="id"></param>
        /// <returns></returns>
        public Notice Web_details(int id)
        {
            con.Open();
            string sql = "select * from Mark Where UserInfoId='" + id + "'";
            SqlDataAdapter adp = new SqlDataAdapter(sql, con);
            DataTable table = new DataTable();
            adp.Fill(table);
            con.Close();
            List<Notice> list = new List<Notice>();
            foreach (DataRow item in table.Rows)
            {
                Notice m = new Notice();
                m.NoticeContent = item["NoticeContent"].ToString();
                m.NoticeId = Convert.ToInt32(item["NoticeId"]);
                m.NoticePerson = item["NoticePerson"].ToString();
                m.NoticeTime = Convert.ToDateTime(item["NoticeTime"]);
                m.NoticeName = item["NoticeName"].ToString();
                list.Add(m);
            }
            return list.FirstOrDefault();
        }
        /// <summary>
        /// 网站公告的修改
        /// </summary>
        /// <param name="id"></param>
        /// <returns></returns>
        public int Web_Edit(Notice m)
        {
            con.Open();
            string sql = "Update Notice set  NoticeContent='" + m.NoticeContent + "',NoticePerson='" + m.NoticePerson + "',NoticeTime='" + m.NoticeTime + "',NoticeName='" + m.NoticeName + "'";
            SqlCommand com = new SqlCommand(sql, con);
            return com.ExecuteNonQuery();
        }
        /// <summary>
        /// 网站公告的搜索
        /// </summary>
        /// <param name="RoleId"></param>
        /// <returns></returns>
        public List<Notice> Web_Search(string NoticeName)
        {
            con.Open();
            string sql = "select NoticeId,NoticeName,NoticeContent,NoticePerson,NoticeTime where NoticeName like'" + NoticeName + "'";
            SqlDataAdapter adp = new SqlDataAdapter(sql, con);
            DataTable table = new DataTable();
            adp.Fill(table);
            con.Close();
            List<Notice> list = new List<Notice>();
            foreach (DataRow item in table.Rows)
            {
                Notice m = new Notice();
                m.NoticeContent = item["NoticeContent"].ToString();
                m.NoticeId = Convert.ToInt32(item["NoticeId"]);
                m.NoticePerson = item["NoticePerson"].ToString();
                m.NoticeTime =Convert.ToDateTime(item["NoticeTime"]);
                m.NoticeName = item["NoticeName"].ToString();
                list.Add(m);
            }
            return list;
        }






    }
}
